{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "name_col=['carid','pushDate','pushPrice','updatePriceTimeJson','pullDate','withdrawDate']\n",
    "data_train=pd.read_table(\"门店交易训练数据.txt\",names=name_col)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>carid</th>\n",
       "      <th>tradetime</th>\n",
       "      <th>brand</th>\n",
       "      <th>serial</th>\n",
       "      <th>model</th>\n",
       "      <th>mileage</th>\n",
       "      <th>color</th>\n",
       "      <th>cityid</th>\n",
       "      <th>carcode</th>\n",
       "      <th>transfercount</th>\n",
       "      <th>seating</th>\n",
       "      <th>registerdate</th>\n",
       "      <th>licenseDate</th>\n",
       "      <th>country</th>\n",
       "      <th>maketype</th>\n",
       "      <th>modelyear</th>\n",
       "      <th>displacement</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>oiltype</th>\n",
       "      <th>newprice</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-06-28</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>2018-01-26</td>\n",
       "      <td>779413.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.88</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4220*1740*1625</td>\n",
       "      <td>201709.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2021-06-25</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8.60</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>2017-03-21</td>\n",
       "      <td>779415.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.98</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1+2</td>\n",
       "      <td>4630*1775*1480</td>\n",
       "      <td>201609.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>15.56</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2008-02-01</td>\n",
       "      <td>2008-02-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>1.6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>12.78</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4515*1725*1445</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6.04</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>2016-09-09</td>\n",
       "      <td>779413.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.49</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-08-18</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4500*1834*1707</td>\n",
       "      <td>201608.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>5.70</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2012-08-01</td>\n",
       "      <td>2012-08-28</td>\n",
       "      <td>779415.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>18.08</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-09-20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4315*1783*1606</td>\n",
       "      <td>201204.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carid   tradetime  brand  serial  model  mileage  color  cityid  carcode  \\\n",
       "0      1  2021-06-28      1       1      1     4.01      1       1      1.0   \n",
       "1      2  2021-06-25      2       2      2     8.60      1       2      1.0   \n",
       "2      5  2021-06-19      5       5      5    15.56      1       2      3.0   \n",
       "3      6  2021-06-29      6       6      6     6.04      1       3      1.0   \n",
       "4      7  2021-06-30      7       7      7     5.70      4       1      2.0   \n",
       "\n",
       "   transfercount  seating registerdate licenseDate   country  maketype  \\\n",
       "0              0        5   2017-12-01  2018-01-26  779413.0       1.0   \n",
       "1              0        5   2016-12-01  2017-03-21  779415.0       2.0   \n",
       "2              0        5   2008-02-01  2008-02-27       NaN       NaN   \n",
       "3              3        5   2016-08-01  2016-09-09  779413.0       1.0   \n",
       "4              2        5   2012-08-01  2012-08-28  779415.0       2.0   \n",
       "\n",
       "   modelyear  displacement  gearbox  oiltype  newprice    0  1  2    3  4  5  \\\n",
       "0     2017.0           1.5      1.0        1      6.88  1.0  1  1  1.0  1  1   \n",
       "1     2017.0           1.2      2.0        1     11.98  1.0  2  2  2.0  2  2   \n",
       "2     2008.0           1.6      4.0        1     12.78  1.0  2  2  5.0  5  2   \n",
       "3     2016.0           1.3      2.0        1      9.49  1.0  5  2  6.0  6  1   \n",
       "4     2012.0           2.0      5.0        1     18.08  1.0  5  2  7.0  7  1   \n",
       "\n",
       "            6    7    8    9   10              11        12  13   14  price  \n",
       "0         NaN  1.0  5.0  2.0    1  4220*1740*1625  201709.0   1  NaN   4.24  \n",
       "1         NaN  2.0  4.0  3.0  1+2  4630*1775*1480  201609.0   2  NaN   7.38  \n",
       "2         NaN  NaN  NaN  NaN  NaN  4515*1725*1445       NaN   2  NaN   1.00  \n",
       "3  2018-08-18  2.0  5.0  2.0    1  4500*1834*1707  201608.0   2  NaN   4.38  \n",
       "4  2020-09-20  1.0  5.0  2.0    1  4315*1783*1606  201204.0   1  NaN   5.90  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a=[i for i in range(15)]\n",
    "b=[i for i in range(35)]\n",
    "c=[i for i in range(34)]\n",
    "colname=['carid','tradetime','brand','serial','model','mileage','color','cityid','carcode','transfercount','seating','registerdate','licenseDate','country','maketype','modelyear','displacement','gearbox','oiltype','newprice']\n",
    "colname=colname+a\n",
    "\n",
    "colname_withoutprice=colname\n",
    "colname=colname+['price']\n",
    "data1=pd.read_table(\"估价训练数据.txt\",names=colname,header=None)\n",
    "data2=pd.read_table(\"估价验证数据.txt\",index_col=0,names=colname_withoutprice)\n",
    "data1.head()\n",
    "#data2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10000 entries, 0 to 9999\n",
      "Data columns (total 6 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   carid                10000 non-null  int64  \n",
      " 1   pushDate             10000 non-null  object \n",
      " 2   pushPrice            10000 non-null  float64\n",
      " 3   updatePriceTimeJson  10000 non-null  object \n",
      " 4   pullDate             10000 non-null  object \n",
      " 5   withdrawDate         8000 non-null   object \n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 468.9+ KB\n"
     ]
    }
   ],
   "source": [
    "data_train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>carid</th>\n",
       "      <th>pushDate</th>\n",
       "      <th>pushPrice</th>\n",
       "      <th>updatePriceTimeJson</th>\n",
       "      <th>pullDate</th>\n",
       "      <th>withdrawDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>NaN</td>\n",
       "      <td>547</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3212</td>\n",
       "      <td>623</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-02-20</td>\n",
       "      <td>2021-02-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>NaN</td>\n",
       "      <td>37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6763</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>35954.907800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.837421</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>22132.368783</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.125339</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>16658.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.680000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>35493.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.800000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>55687.250000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.800000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>74156.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>658.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               carid    pushDate     pushPrice updatePriceTimeJson  \\\n",
       "count   10000.000000       10000  10000.000000               10000   \n",
       "unique           NaN         547           NaN                3212   \n",
       "top              NaN  2021-06-18           NaN                  {}   \n",
       "freq             NaN          37           NaN                6763   \n",
       "mean    35954.907800         NaN     13.837421                 NaN   \n",
       "std     22132.368783         NaN     15.125339                 NaN   \n",
       "min         2.000000         NaN      0.100000                 NaN   \n",
       "25%     16658.500000         NaN      5.680000                 NaN   \n",
       "50%     35493.000000         NaN      9.800000                 NaN   \n",
       "75%     55687.250000         NaN     17.800000                 NaN   \n",
       "max     74156.000000         NaN    658.000000                 NaN   \n",
       "\n",
       "          pullDate withdrawDate  \n",
       "count        10000         8000  \n",
       "unique         623          610  \n",
       "top     2021-02-20   2021-02-20  \n",
       "freq            70           60  \n",
       "mean           NaN          NaN  \n",
       "std            NaN          NaN  \n",
       "min            NaN          NaN  \n",
       "25%            NaN          NaN  \n",
       "50%            NaN          NaN  \n",
       "75%            NaN          NaN  \n",
       "max            NaN          NaN  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>carid</th>\n",
       "      <th>pushDate</th>\n",
       "      <th>pushPrice</th>\n",
       "      <th>updatePriceTimeJson</th>\n",
       "      <th>pullDate</th>\n",
       "      <th>withdrawDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>68603</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>3.9800</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2021-03-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12312</td>\n",
       "      <td>2021-05-14</td>\n",
       "      <td>4.5000</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>2021-06-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57655</td>\n",
       "      <td>2021-03-13</td>\n",
       "      <td>23.9000</td>\n",
       "      <td>{\"2021-04-05\": \"23\"}</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>2021-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45688</td>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>20.5798</td>\n",
       "      <td>{}</td>\n",
       "      <td>2020-09-04</td>\n",
       "      <td>2020-09-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52081</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>12.2800</td>\n",
       "      <td>{\"2021-05-20\": \"11.9\"}</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carid    pushDate  pushPrice     updatePriceTimeJson    pullDate  \\\n",
       "0  68603  2021-03-11     3.9800                      {}  2021-03-11   \n",
       "1  12312  2021-05-14     4.5000                      {}  2021-06-14   \n",
       "2  57655  2021-03-13    23.9000    {\"2021-04-05\": \"23\"}  2021-04-08   \n",
       "3  45688  2020-09-01    20.5798                      {}  2020-09-04   \n",
       "4  52081  2021-04-29    12.2800  {\"2021-05-20\": \"11.9\"}  2021-06-21   \n",
       "\n",
       "  withdrawDate  \n",
       "0   2021-03-11  \n",
       "1   2021-06-14  \n",
       "2   2021-04-08  \n",
       "3   2020-09-04  \n",
       "4   2021-06-21  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_process(date1,date2):\n",
    "    year1 = int(str(date1)[:4])\n",
    "    month1 = int(str(date1)[6:7])\n",
    "    day1 = int(str(date1)[9:])\n",
    "    year2 = int(str(date2)[:4])\n",
    "    month2 = int(str(date2)[6:7])\n",
    "    day2 = int(str(date2)[9:])\n",
    "    date=(year2-year1)*365+(month2-month1)*30+(day2+day1)\n",
    "\n",
    "    return date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10000 entries, 0 to 9999\n",
      "Data columns (total 7 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   carid                10000 non-null  int64         \n",
      " 1   pushDate             10000 non-null  datetime64[ns]\n",
      " 2   pushPrice            10000 non-null  float64       \n",
      " 3   updatePriceTimeJson  10000 non-null  object        \n",
      " 4   pullDate             10000 non-null  object        \n",
      " 5   withdrawDate         8000 non-null   datetime64[ns]\n",
      " 6   transcycle           8000 non-null   float64       \n",
      "dtypes: datetime64[ns](2), float64(2), int64(1), object(2)\n",
      "memory usage: 547.0+ KB\n"
     ]
    }
   ],
   "source": [
    "data_train['pushDate'] = pd.to_datetime(data_train['pushDate'])\n",
    "data_train['withdrawDate'] = pd.to_datetime(data_train['withdrawDate'])\n",
    "trans_circle = pd.DataFrame(data_train['withdrawDate'] - data_train['pushDate'])\n",
    "data_train['transcycle'] = trans_circle[0]\n",
    "#data_train.insert(loc=7,column='price',value=0)\n",
    "data_train['transcycle']=data_train['transcycle'].astype('timedelta64[D]').astype(float)\n",
    "data_train.head()\n",
    "data_train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>carid</th>\n",
       "      <th>pushDate</th>\n",
       "      <th>pushPrice</th>\n",
       "      <th>updatePriceTimeJson</th>\n",
       "      <th>pullDate</th>\n",
       "      <th>withdrawDate</th>\n",
       "      <th>transcycle</th>\n",
       "      <th>cycle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>68603</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>3.9800</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12312</td>\n",
       "      <td>2021-05-14</td>\n",
       "      <td>4.5000</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57655</td>\n",
       "      <td>2021-03-13</td>\n",
       "      <td>23.9000</td>\n",
       "      <td>{\"2021-04-05\": \"23\"}</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45688</td>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>20.5798</td>\n",
       "      <td>{}</td>\n",
       "      <td>2020-09-04</td>\n",
       "      <td>2020-09-04</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52081</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>12.2800</td>\n",
       "      <td>{\"2021-05-20\": \"11.9\"}</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0 days</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carid   pushDate  pushPrice     updatePriceTimeJson   pullDate  \\\n",
       "0  68603 2021-03-11     3.9800                      {} 2021-03-11   \n",
       "1  12312 2021-05-14     4.5000                      {} 2021-06-14   \n",
       "2  57655 2021-03-13    23.9000    {\"2021-04-05\": \"23\"} 2021-04-08   \n",
       "3  45688 2020-09-01    20.5798                      {} 2020-09-04   \n",
       "4  52081 2021-04-29    12.2800  {\"2021-05-20\": \"11.9\"} 2021-06-21   \n",
       "\n",
       "  withdrawDate  transcycle  cycle  \n",
       "0   2021-03-11         0.0 0 days  \n",
       "1   2021-06-14        31.0 0 days  \n",
       "2   2021-04-08        26.0 0 days  \n",
       "3   2020-09-04         3.0 0 days  \n",
       "4   2021-06-21        53.0 0 days  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train['pullDate'] = pd.to_datetime(data_train['pullDate'])\n",
    "data_train['withdrawDate'] = pd.to_datetime(data_train['withdrawDate'])\n",
    "trans_circle = pd.DataFrame(data_train['withdrawDate'] - data_train['pullDate'])\n",
    "data_train['cycle'] = trans_circle[0]\n",
    "data_train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "取成交的部分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\LENOVO\\anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:4906: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  return super().drop(\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>carid</th>\n",
       "      <th>pushDate</th>\n",
       "      <th>pushPrice</th>\n",
       "      <th>updatePriceTimeJson</th>\n",
       "      <th>pullDate</th>\n",
       "      <th>withdrawDate</th>\n",
       "      <th>transcycle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>68603</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>3.9800</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12312</td>\n",
       "      <td>2021-05-14</td>\n",
       "      <td>4.5000</td>\n",
       "      <td>{}</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57655</td>\n",
       "      <td>2021-03-13</td>\n",
       "      <td>23.9000</td>\n",
       "      <td>{\"2021-04-05\": \"23\"}</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45688</td>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>20.5798</td>\n",
       "      <td>{}</td>\n",
       "      <td>2020-09-04</td>\n",
       "      <td>2020-09-04</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52081</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>12.2800</td>\n",
       "      <td>{\"2021-05-20\": \"11.9\"}</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carid   pushDate  pushPrice     updatePriceTimeJson   pullDate  \\\n",
       "0  68603 2021-03-11     3.9800                      {} 2021-03-11   \n",
       "1  12312 2021-05-14     4.5000                      {} 2021-06-14   \n",
       "2  57655 2021-03-13    23.9000    {\"2021-04-05\": \"23\"} 2021-04-08   \n",
       "3  45688 2020-09-01    20.5798                      {} 2020-09-04   \n",
       "4  52081 2021-04-29    12.2800  {\"2021-05-20\": \"11.9\"} 2021-06-21   \n",
       "\n",
       "  withdrawDate  transcycle  \n",
       "0   2021-03-11         0.0  \n",
       "1   2021-06-14        31.0  \n",
       "2   2021-04-08        26.0  \n",
       "3   2020-09-04         3.0  \n",
       "4   2021-06-21        53.0  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data3=data_train[data_train['cycle']=='0 days']\n",
    "data4=data3[data3['updatePriceTimeJson']=='{}']\n",
    "data5=data3[data3['updatePriceTimeJson']!='{}']\n",
    "data6=data_train[data_train['cycle']!='0 days']\n",
    "data3.drop('cycle',axis=1, inplace=True)\n",
    "data4.drop('cycle',axis=1, inplace=True)#未改价格\n",
    "data5.drop('cycle',axis=1, inplace=True)#改价格\n",
    "data6.drop('cycle',axis=1, inplace=True)#未买出\n",
    "data3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "遍历不明白"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for i in range(len(data1['carid'])):\n",
    "    #for j in range(len(data_train['carid'])):\n",
    "       # if data1.iloc[i,0]==data_train.iloc[j,0]:\n",
    "            #data_train.iloc[j,-1]=data1.iloc[i,-1]"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "7dd44356432448c335aab49bb4a35eec45732cd176bb87a60d07cce9b4b0faba"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
